//
//  PostMessageDataBase.m
//  SIPphone
//
//  Created by wrt on 13-12-16.
//  Copyright (c) 2013年 wrtsoft. All rights reserved.
//

#import "PostMessageDataBase.h"

//数据库对象的单例
static PostMessageDataBase *sharePostMessageDataBase = nil;

@implementation PostMessageDataBase

//单例的初始化方法
+ (PostMessageDataBase *)sharePostMessageDateBase
{
    @synchronized(self)
    {
        if (sharePostMessageDataBase == nil)
        {
            sharePostMessageDataBase = [[self alloc] init];
        }
    }
    return sharePostMessageDataBase;
}
//创建数据库
- (void)createDb
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"postMessage.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //创建表
        char *error;
        NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT)",self.tableNameForUser,POST_TIME,POST_TEXT,POST_STATUS,POST_PATH,POST_NOTICE,POST_KIND];
        //运行SQL语句
        if(sqlite3_exec(db, [createSQL UTF8String], NULL, NULL, &error) !=SQLITE_OK
           )
        {
            NSLog(@"建表失败！%s",error);
        }
        //关闭数据库
        sqlite3_close(db);
    }
}

//切换表名
- (void)changeTableName:(NSString *)userName
{
    self.tableNameForUser = [[NSString alloc] init];
    self.tableNameForUser = [NSString stringWithFormat:@"postmsg_%@",userName];
}

//读取数据库中的数值
- (void)loadArray
{
    //将所需要存数据的数组初始化
    self.timeArray = [[NSMutableArray alloc] init];
    self.textArray = [[NSMutableArray alloc] init];
    self.statusArray = [[NSMutableArray alloc] init];
    self.pathArray = [[NSMutableArray alloc] init];
    self.noticeArray = [[NSMutableArray alloc] init];
    self.kindArray = [[NSMutableArray alloc] init];
    
    //数据库先执行一遍读
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"postMessage.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT %@,%@,%@,%@,%@,%@ FROM %@ ORDER BY %@ ",POST_TIME,POST_TEXT,POST_STATUS,POST_PATH,POST_NOTICE,POST_KIND,self.tableNameForUser,POST_TIME];
        
        sqlite3_stmt *statement;
        
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            //执行
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                char *text1 = (char *)sqlite3_column_text(statement, 0);
                self.tempTime = [[NSString alloc] initWithUTF8String:text1];
                [self.timeArray addObject:self.tempTime];
                
                char *text2 = (char *)sqlite3_column_text(statement, 1);
                self.tempText = [[NSString alloc] initWithUTF8String:text2];
                [self.textArray addObject:self.tempText];
                
                char *text3 = (char *)sqlite3_column_text(statement, 2);
                self.tempStatus = [[NSString alloc] initWithUTF8String:text3];
                [self.statusArray addObject:self.tempStatus];
                
                char *text4 = (char *)sqlite3_column_text(statement, 3);
                self.tempUrlPath = [[NSString alloc] initWithUTF8String:text4];
                [self.pathArray addObject:self.tempUrlPath];
                
                char *text5 = (char *)sqlite3_column_text(statement, 4);
                self.tempNotice = [[NSString alloc] initWithUTF8String:text5];
                [self.noticeArray addObject:self.tempNotice];
                
                char *text6 = (char *)sqlite3_column_text(statement, 5);
                self.tempKind = [[NSString alloc] initWithUTF8String:text6];
                [self.kindArray addObject:self.tempKind];
            }
        }
        //释放状态资源
        sqlite3_finalize(statement);
        //关闭数据库
        sqlite3_close(db);
    }
}

//写进数据库
- (void)savePostMsg:(NSString *)time andKind:(NSString *)kind andNotice:(NSString *)notice andPath:(NSString *)path andText:(NSString *)text
{
    //数据库写操作
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"postMessage.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@,%@,%@) VALUES(?,?,?,?,?,?)",self.tableNameForUser,POST_TIME,POST_TEXT,POST_STATUS,POST_PATH,POST_NOTICE,POST_KIND];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            //绑定参数
            sqlite3_bind_text(statment, 1, [time UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 2, [text UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 3, [@"未读" UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 4, [path UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 5, [notice UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 6, [kind UTF8String], -1, NULL);
            //执行插入
            if(sqlite3_step(statment) !=SQLITE_DONE)
            {
                NSLog(@"插入失败");
            }
        }
        
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
    [self loadArray];
}
//修改数据库未读状态的方法
- (void)editStatusWithTime:(NSString *)time andText:(NSString *)text
{
    //数据库执行修改
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"postMessage.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *tempTime = [[NSString alloc] init];
        tempTime = time;
        NSString *tempText = [[NSString alloc] init];
        tempText = text;
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,POST_STATUS,@"已读",POST_TIME,tempTime,POST_TEXT,tempText];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
    [self loadArray];
}
//删除单条数据库
- (void)deleteWithTime:(NSString *)time andText:(NSString *)text
{
    //数据库执行删除
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"postMessage.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,POST_TIME,time,POST_TEXT,text];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
}
//删除全部
- (void)removeAll
{
    //数据库执行删除
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"postMessage.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE 1 > 0",self.tableNameForUser];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
}

//获取未读的条数
- (int)countForUnreadWith
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"postMessage.db"];
    
    int i = 0;
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ WHERE %@ = '%@'",self.tableNameForUser,POST_STATUS,@"未读"];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statment) == SQLITE_ROW)
            {
                i = sqlite3_column_int(statment, 0);
            }
        }
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
    return i;
}

//获取部分信息15一组
- (void)readDBForpartWithBeginNumber:(int)begin
{
    self.partTimeArray = [[NSMutableArray alloc] init];
    self.partTextArray = [[NSMutableArray alloc] init];
    self.partStatusArray = [[NSMutableArray alloc] init];
    self.partPathArray = [[NSMutableArray alloc] init];
    self.partNoticeArray = [[NSMutableArray alloc] init];
    self.partKindArray = [[NSMutableArray alloc] init];
    
    //数据库先执行一遍读
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"postMessage.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT %@,%@,%@,%@,%@,%@ FROM %@ ORDER BY %@ desc limit %d,15",POST_TIME,POST_TEXT,POST_STATUS,POST_PATH,POST_NOTICE,POST_KIND,self.tableNameForUser,POST_TIME,begin];
        
        sqlite3_stmt *statement;
        
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            //执行
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                char *text1 = (char *)sqlite3_column_text(statement, 0);
                self.tempTime = [[NSString alloc] initWithUTF8String:text1];
                [self.partTimeArray addObject:self.tempTime];
                
                char *text2 = (char *)sqlite3_column_text(statement, 1);
                self.tempText = [[NSString alloc] initWithUTF8String:text2];
                [self.partTextArray addObject:self.tempText];
                
                char *text3 = (char *)sqlite3_column_text(statement, 2);
                self.tempStatus = [[NSString alloc] initWithUTF8String:text3];
                [self.partStatusArray addObject:self.tempStatus];
                
                char *text4 = (char *)sqlite3_column_text(statement, 3);
                self.tempUrlPath = [[NSString alloc] initWithUTF8String:text4];
                [self.partPathArray addObject:self.tempUrlPath];
                
                char *text5 = (char *)sqlite3_column_text(statement, 4);
                self.tempNotice = [[NSString alloc] initWithUTF8String:text5];
                [self.partNoticeArray addObject:self.tempNotice];
                
                char *text6 = (char *)sqlite3_column_text(statement, 5);
                self.tempKind = [[NSString alloc] initWithUTF8String:text6];
                [self.partKindArray addObject:self.tempKind];
                
            }
            //释放状态资源
            sqlite3_finalize(statement);
            //关闭数据库
            sqlite3_close(db);
        }
    }
}

@end
